/**
 * 
 */
package com.probiz.estore.report.service;

import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.support.rowset.SqlRowSet;

import com.probiz.estore.order.OrderConstants;

/**
 * 
 * 获取报表数据的引擎，JDBC调用方式
 * 
 *
 */
public class ReportDataEngine {
	private org.springframework.jdbc.core.JdbcTemplate jdbcTemplate;
	
	public static String SALES_AMOUNT_BY_MONTH = "SELECT DAY(updateTime) as 'name', SUM(paidAmount) as 'value' FROM sales_order s WHERE (s.orderStatus = "+OrderConstants.ORDER_STATUS_COMPLETE+") AND YEAR(updateTime)=? AND MONTH(updateTime)=? group by DAY(updateTime) ORDER BY DAY(updateTime)";

	public static String SALES_AMOUNT_BY_YEAR = "SELECT MONTH(updateTime) as 'name', SUM(paidAmount) as 'value' FROM sales_order s WHERE (s.orderStatus = "+OrderConstants.ORDER_STATUS_COMPLETE+") AND  YEAR(updateTime)=? group by MONTH(updateTime) ORDER BY MONTH(updateTime)";
	
	public static String ORDER_COUNT_TREND_BY_MONTH = "SELECT DAY(createTime) as 'name', COUNT(*) as 'value' FROM sales_order s WHERE YEAR(createTime)=? AND MONTH(createTime)=? group by DAY(createTime) ORDER BY DAY(createTime)";

	public static String ORDER_COUNT_TREND_BY_YEAR = "SELECT MONTH(createTime) as 'name', COUNT(*) as 'value' FROM sales_order s WHERE YEAR(createTime)=? group by MONTH(createTime) ORDER BY MONTH(createTime)";
	
	public static String CUSTOMER_REG_TREND_BY_MONTH = "SELECT DAY(createTime) as 'name', COUNT(*) as 'value' FROM app_user s WHERE s.userType=0 AND YEAR(createTime)=? AND MONTH(createTime)=? group by DAY(createTime) ORDER BY DAY(createTime)";

	public static String CUSTOMER_REG_TREND_BY_YEAR = "SELECT MONTH(createTime) as 'name', COUNT(*) as 'value' FROM app_user s WHERE s.userType=0 AND YEAR(createTime)=? group by MONTH(createTime) ORDER BY MONTH(createTime)";
	
	//主面板
	public static String ORDER_COUNT_TREND_BY_PERIOD = "SELECT DAY(createTime), COUNT(*) as 'value' FROM sales_order s WHERE s.createTime>=? AND s.createTime<=? group by DAY(createTime) ORDER BY DAY(createTime)";
	public static String CUSTOMER_COUNT_TREND_BY_PERIOD = "SELECT DAY(createTime), COUNT(*) as 'value' FROM app_user s WHERE s.userType=0 AND s.createTime>=? AND s.createTime<=? group by DAY(createTime) ORDER BY DAY(createTime)";
	
	public Map<String, Object> getReportData(String reprotSql, final Object... paramValues){
		SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet(reprotSql, paramValues);
		Map<String, Object> map = new HashMap<String, Object>();
		while(sqlRowSet.next()){
			map.put(sqlRowSet.getString(1), sqlRowSet.getObject(2));
		}
		
		return map;
	}
	
	public List<Object[]> getProductSalesRankData(final Integer topN, final String startTime, final String endTime){
		String reprotSql = "SELECT sku.productSkuCode, sku.productName,sku.displaySkuOptions, SUM(sku.deliveryQuantity) FROM sales_order s,  order_sku sku WHERE (s.orderStatus = "+OrderConstants.ORDER_STATUS_COMPLETE+") AND s.salesOrderId=sku.salesOrderId   and s.updateTime>=? and s.updateTime<=? and sku.itemType=1 group by sku.productSkuId ORDER BY SUM(sku.deliveryQuantity) desc LIMIT ?"; 
		SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet(reprotSql, new Object[]{startTime, endTime, topN});
		List<Object[]> list = new ArrayList<Object[]>();
		int i = 1;
		while(sqlRowSet.next()){
			Object[] row = new Object[]{i++, sqlRowSet.getObject(1), sqlRowSet.getObject(2), sqlRowSet.getObject(3),sqlRowSet.getObject(4)};
			list.add(row);
		}
		return list;
	}
	
	public List<Object[]> getCustomerBuyRankData(final Integer topN, final String startTime, final String endTime){
		String reprotSql = "SELECT s.customerId, concat(u.username, CASE WHEN (u.firstname is null or u.firstname='') THEN '' ELSE concat(' (', u.firstname, ')') END), SUM(s.totalAmount), COUNT(*) FROM sales_order s join app_user u on s.customerId=u.appuserId  WHERE (s.orderStatus = "+OrderConstants.ORDER_STATUS_COMPLETE+") and s.customerId!=-2 and s.updateTime>=? and s.updateTime<=? group by s.customerId ORDER BY SUM(s.totalAmount) desc LIMIT ?"; 
		SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet(reprotSql, new Object[]{startTime, endTime, topN});
		List<Object[]> list = new ArrayList<Object[]>();
		int i = 1;
		while(sqlRowSet.next()){
			Object[] row = new Object[]{i++, sqlRowSet.getObject(1), sqlRowSet.getObject(2), sqlRowSet.getObject(3), sqlRowSet.getObject(4)};
			list.add(row);
		}
		return list;
	}
	public Map<String,Object[]> getSalesIndicatorData(final String startTime, final String endTime){
		Map<String, Object[]> map = new HashMap<String, Object[]>();
		
		//发生过订单的客户量
		Long customerBuyCount = 0L;
		//总客户量
		Long allCustomerCount = 0L;
		//订单总金额
		BigDecimal allOrderTotalAmount = null;
		//订单总量
		Long allOrderCount = 0L;
		//订单下的商品访问总次数
		Long orderSkuViewCount = 0L;
		
		if(startTime!=null && endTime!=null){
			String customerBuySql = "SELECT COUNT(*) FROM (SELECT distinct s.customerId  FROM sales_order s WHERE (s.orderStatus = "+OrderConstants.ORDER_STATUS_COMPLETE+") and s.createTime>=? and s.createTime<=?) c";
			customerBuyCount = jdbcTemplate.queryForLong(customerBuySql, new Object[]{startTime, endTime});
			
			String allCustomerCountSql = "SELECT COUNT(*) FROM app_user WHERE userType=0 AND createTime<=? AND deleted=0 AND appuserId!=-2";
			allCustomerCount = jdbcTemplate.queryForLong(allCustomerCountSql, new Object[]{endTime});
			
			String allOrderTotalAmountSql = "SELECT SUM(s.totalAmount) FROM sales_order s WHERE s.createTime>=? and s.createTime<=?";
			allOrderTotalAmount = (BigDecimal)jdbcTemplate.queryForObject(allOrderTotalAmountSql, new Object[]{startTime, endTime}, BigDecimal.class);
			
			String allOrderCountSql = "SELECT COUNT(*) FROM sales_order s WHERE s.createTime>=? and s.createTime<=?";
			allOrderCount = jdbcTemplate.queryForLong(allOrderCountSql, new Object[]{startTime, endTime});
			
			String orderSkuViewCountSql = "SELECT SUM(ps.viewCount) FROM (SELECT distinct os.productId FROM order_sku as os) as disOs LEFT JOIN product as p ON disOs.productId=p.productId LEFT JOIN product_stat as ps ON ps.productStatId=p.productStatId";
			orderSkuViewCount = jdbcTemplate.queryForLong(orderSkuViewCountSql);
			
		}else{
			
			String customerBuySql = "SELECT COUNT(*) FROM (SELECT distinct s.customerId  FROM sales_order s) c";
			customerBuyCount = jdbcTemplate.queryForLong(customerBuySql);
			
			String allCustomerCountSql = "SELECT COUNT(*) FROM app_user WHERE userType=0 AND deleted=0 AND appuserId!=-2";
			allCustomerCount = jdbcTemplate.queryForLong(allCustomerCountSql);
			
			String allOrderTotalAmountSql = "SELECT SUM(s.totalAmount) FROM sales_order s";
			allOrderTotalAmount = (BigDecimal)jdbcTemplate.queryForObject(allOrderTotalAmountSql, BigDecimal.class);
			
			String allOrderCountSql = "SELECT COUNT(*) FROM sales_order s";
			allOrderCount = jdbcTemplate.queryForLong(allOrderCountSql);
			
			String orderSkuViewCountSql = "SELECT SUM(ps.viewCount) FROM (SELECT distinct os.productId FROM order_sku as os) as disOs LEFT JOIN product as p ON disOs.productId=p.productId LEFT JOIN product_stat as ps ON ps.productStatId=p.productStatId";
			orderSkuViewCount = jdbcTemplate.queryForLong(orderSkuViewCountSql);
		}
		
		DecimalFormat rateDf = new DecimalFormat("0.00%");
		if(null == allOrderTotalAmount){
			allOrderTotalAmount = new BigDecimal("0");
		}
		//注册客户购买率
		map.put("customerBuyRate", new Object[]{customerBuyCount, allCustomerCount, allCustomerCount==0?"0":rateDf.format((float)customerBuyCount/allCustomerCount)});
		//客户平均订单金额
		map.put("orderAmountAvg", new Object[]{allOrderTotalAmount.setScale(2, BigDecimal.ROUND_DOWN), allOrderCount, allOrderCount==0?"0":allOrderTotalAmount.divide(new BigDecimal(allOrderCount), 2, BigDecimal.ROUND_DOWN )});
		//客户平均订单量
		map.put("orderCountAvg", new Object[]{allOrderCount, allCustomerCount, allCustomerCount==0?"0":(float)allOrderCount/allCustomerCount});
		//订单转化率
		map.put("orderConversionRate", new Object[]{allOrderCount, orderSkuViewCount, orderSkuViewCount==0?"0":rateDf.format((float)allOrderCount/orderSkuViewCount)});
		//每次访问平均订单金额
		map.put("orderAmountAvgOneView", new Object[]{allOrderTotalAmount.setScale(2, BigDecimal.ROUND_DOWN), orderSkuViewCount, orderSkuViewCount==0?"0":allOrderTotalAmount.divide(new BigDecimal(orderSkuViewCount), 2, BigDecimal.ROUND_DOWN )});
		
		return map;
	}
	
	public Map<String, Map<String, Long>> getOrderAndCustomerTrendRecently(Date startDate, Date endDate){
		Map<String, Map<String, Long>> ocr = new HashMap<String, Map<String, Long>>();
		
		SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet(ORDER_COUNT_TREND_BY_PERIOD, new Object[]{startDate, endDate});	
		Map<String, Long> map = new HashMap<String, Long>();
		while(sqlRowSet.next()){
			map.put(sqlRowSet.getString(1), sqlRowSet.getLong(2));
		}
		ocr.put("salesOrder", map);
		
		SqlRowSet sqlRowSet2 = jdbcTemplate.queryForRowSet(CUSTOMER_COUNT_TREND_BY_PERIOD, new Object[]{startDate, endDate});	
		Map<String, Long> map2 = new HashMap<String, Long>();
		while(sqlRowSet2.next()){
			map2.put(sqlRowSet2.getString(1), sqlRowSet2.getLong(2));
		}
		ocr.put("customer", map2);
		return ocr;
	}
	
	public void setJdbcTemplate(org.springframework.jdbc.core.JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
}
